/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import models.Person;

import com.mysql.jdbc.Statement;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author jaime.castells
 */
public class PersonDB {
    
    public PersonDB(){
        
    }
    
    public ArrayList<Person> obtenerPersonas(){
        ArrayList<Person> lista = new ArrayList<Person>();
        ArrayList<String> Knowledges = new ArrayList<String>();
        ArrayList<Integer> KnowledgesWeights = new ArrayList<Integer>();
        ArrayList<String> Qualities = new ArrayList<String>();
        ArrayList<Integer> QualitiesWeights = new ArrayList<Integer>();
        ArrayList<String> studies = new ArrayList<String>();
        java.sql.ResultSet rs;
        Person a = null;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs =  st.executeQuery("SELECT * FROM person"); 
            while (rs.next()){
                a = new Person(rs.getInt("idPerson"),rs.getObject("firstName").toString(),rs.getObject("lastName").toString(),rs.getObject("gender").toString(),rs.getObject("city").toString(),rs.getInt("age"),rs.getInt("years"));
                lista.add(a);
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        for (int i=0;i<lista.size();i++){
            Knowledges = new ArrayList<String>();
            KnowledgesWeights = new ArrayList<Integer>();
            Qualities = new ArrayList<String>();
            QualitiesWeights = new ArrayList<Integer>();
            studies = new ArrayList<String>();
	        try {
	            Conexion c = new Conexion();
	            Statement st = (Statement) c.getConnection().createStatement();
	            rs =  st.executeQuery("SELECT * FROM knowledges WHERE idPerson='" + lista.get(i).getId()+"'"); 
	            while (rs.next()){
	            	String s = new String(rs.getObject("knowledge").toString());
	            	int r = rs.getInt("weight");
	                Knowledges.add(s);
	                KnowledgesWeights.add(r);
	            }
	        } catch (SQLException ex) {
	            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
	        }
	        try {
	            Conexion c = new Conexion();
	            Statement st = (Statement) c.getConnection().createStatement();
	            rs =  st.executeQuery("SELECT * FROM qualities WHERE idPerson='" + lista.get(i).getId()+"'"); 
	            while (rs.next()){
	            	String s = new String(rs.getObject("quality").toString());
	            	int r = rs.getInt("weight");
	                Qualities.add(s);
	                QualitiesWeights.add(r);
	            }
	        } catch (SQLException ex) {
	            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
	        }
	        try {
	            Conexion c = new Conexion();
	            Statement st = (Statement) c.getConnection().createStatement();
	            rs =  st.executeQuery("SELECT * FROM studies WHERE idPerson='" + lista.get(i).getId()+"'"); 
	            while (rs.next()){
	            	String s = new String(rs.getObject("study").toString());
	                studies.add(s);
	            }
	        } catch (SQLException ex) {
	            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
	        }
	        try {
	            Conexion c = new Conexion();
	            Statement st = (Statement) c.getConnection().createStatement();
	            rs =  st.executeQuery("SELECT * FROM contact_information WHERE idPerson='" + lista.get(i).getId()+"'"); 
	            while (rs.next()){
	            	String contact = new String(rs.getObject("contact").toString());
	            	lista.get(i).setInformationContact(contact);
	            }
	        } catch (SQLException ex) {
	            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
	        }
	        lista.get(i).setKnowledges(Knowledges);
	        lista.get(i).setKnowledgesWeights(KnowledgesWeights);
	        lista.get(i).setQualities(Qualities);
	        lista.get(i).setQualitiesWeights(QualitiesWeights);
	        lista.get(i).setStudies(studies);
        }
        
        return lista;
    }
    
    public void guardarEnBase(Person P){
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            int id = P.getId();
            int knowledgeid = obtenerUltimoIdKnowledges();
            st.executeUpdate("INSERT INTO person (idPerson,firstName,lastName,age,city,gender,years) VALUES ('"+id+"','"+P.getFirstName()+"','"+P.getLastname()+"','"+P.getAge()+"','"+P.getCity()+"','"+P.getGender()+"','"+P.getYears()+"')");
            for (int i=0;i<P.getKnowledges().size();i++)
            	st.executeUpdate("INSERT INTO knowledges (idKnowledge,Knowledge,weight,idPerson) VALUES ('"+(knowledgeid+i)+"','"+P.getKnowledges().get(i)+"','"+P.getKnowledgesWeights().get(i)+"','"+id+"')");
            int qualityid = obtenerUltimoIdQualities();
            for (int i=0;i<P.getQualities().size();i++)
            	st.executeUpdate("INSERT INTO qualities (idQuality,quality,weight,idPerson) VALUES ('"+(qualityid+i)+"','"+P.getQualities().get(i)+"','"+P.getQualitiesWeights().get(i)+"','"+id+"')");
            int studiesid = obtenerUltimoIdStudies();
            for (int i=0;i<P.getStudies().size();i++)
            	st.executeUpdate("INSERT INTO studies (idStudy,study,idPerson) VALUES ('"+(studiesid+i)+"','"+P.getStudies().get(i)+"','"+id+"')");
            int contactid = obtenerUltimoIdContact();
            st.executeUpdate("INSERT INTO contact_information (idContact,contact,idPerson) VALUES ('"+contactid+"','"+P.getInformationContact()+"','"+id+"')");
            
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            
    }

    
    public static int obtenerUltimoId(){
        java.sql.ResultSet rs;
        int numero=0;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs  = st.executeQuery("SELECT idPerson FROM person ORDER BY idPerson DESC LIMIT 0,1"); 
            while(rs.next()){
                numero = rs.getInt("idPerson");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return numero+1;
    }
    
    public static int obtenerUltimoIdKnowledges(){
        java.sql.ResultSet rs;
        int numero=0;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs  = st.executeQuery("SELECT idKnowledge FROM knowledges ORDER BY idKnowledge DESC LIMIT 0,1"); 
            while(rs.next()){
                numero = rs.getInt("idKnowledge");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return numero+1;
    }
    
    public static int obtenerUltimoIdQualities(){
        java.sql.ResultSet rs;
        int numero=0;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs  = st.executeQuery("SELECT idQuality FROM qualities ORDER BY idQuality DESC LIMIT 0,1"); 
            while(rs.next()){
                numero = rs.getInt("idQuality");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return numero+1;
    }

    public static int obtenerUltimoIdStudies(){
        java.sql.ResultSet rs;
        int numero=0;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs  = st.executeQuery("SELECT idStudy FROM studies ORDER BY idStudy DESC LIMIT 0,1"); 
            while(rs.next()){
                numero = rs.getInt("idStudy");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return numero+1;
    }
    
    public static int obtenerUltimoIdContact(){
        java.sql.ResultSet rs;
        int numero=0;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs  = st.executeQuery("SELECT idContact FROM contact_information ORDER BY idContact DESC LIMIT 0,1"); 
            while(rs.next()){
                numero = rs.getInt("idContact");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return numero+1;
    }
}
